CREATE procedure register_and_login
    @C_M int,--是顾客1还是商家0，由前端给后端，后端给数据库
    @phone_number varchar(15),
    @password varchar(30),
    @status int out --返回1为允许登录 返回2为密码错误 0为封禁账号 3为不符合
as
declare @isExistC int, -- 存在为1，不存在为0
@isExistM int, -- 存在为1，不存在为0
@isExist int,
@username varchar(20), -- 设置用户名
@hash_password varchar(100), -- 加密后的密码
@hash_password_temp varchar(100),
@timestamp varchar(50),
@year varchar(2),
@month varchar(2),
@date varchar(2),
@hour varchar(2),
@minute varchar(2),
@second varchar(2),
@ms varchar(7),
@employee_number varchar(10),
@forbidden_time  date

    if(@phone_number is null)
        begin
            set @status=3
            return
        end

    if(@password is null)
        begin
            set @status=3
            return
        end



select @isExistC=count(*)
from Customer
where customer_phone_number=@phone_number

select @isExistM=count(*)
from Merchant
where merchant_phone_number=@phone_number

    set @isExist= @isExistC+@isExistM

    if @isExist=0 -- 注册操作
        begin
            set @hash_password=sys.fn_VarBinToHexStr(HASHBYTES('SHA2_256',@password));--密码加密
            set @timestamp = SYSDATETIME()
            set @year = SUBSTRING(@timestamp,3,2)
            set @month = SUBSTRING(@timestamp,6,2)
            set @date = SUBSTRING(@timestamp,9,2)
            set @hour = SUBSTRING(@timestamp,12,2)
            set @minute = SUBSTRING(@timestamp,15,2)
            set @second = SUBSTRING(@timestamp,18,2)
            set @ms = SUBSTRING(@timestamp,21,7)
            if @C_M=1-- 顾客
                begin
                    set @username = 'c'+@year+@month+@date+@hour+@minute+@second+@ms
                    insert into customer(customer_phone_number,customer_username,password)
                    values(@phone_number,@username,@hash_password)
                    print STR(@phone_number,11)+LEFT(@username,1)
                    set @status=1
                    return
                end
            else--商家
                begin
                    set @username = 'm'+@year+@month+@date+@hour+@minute+@second+@ms
                    insert into merchant(merchant_phone_number,merchant_username,password,sales_volume)
                    values(@phone_number,@username,@hash_password,0)
                    print STR(@phone_number,11)+LEFT(@username,1)
                    set @status=1
                    return
                end
        end

    else--登录操作
        begin
            if @C_M=1-- 顾客
                begin
                    select @employee_number = (select employee_number from Customer where customer_phone_number=@phone_number)
                    if @employee_number is null
                        begin
                            set @hash_password=sys.fn_VarBinToHexStr(HASHBYTES('SHA2_256',@password))
                            set @hash_password_temp=(select password from customer where customer_phone_number=@phone_number)
                            if (@hash_password_temp=@hash_password)
                                begin
                                    print STR(@phone_number,11)+LEFT(@username,1)
                                    set @status=1
                                    return
                                end
                            else
                                begin
                                    print '密码错误'
                                    set @status=2
                                    return
                                end
                        end
                    else
                        begin
                            select @forbidden_time = (select forbidden_time from Customer where customer_phone_number=@phone_number)
                            print CONVERT(varchar(100), @forbidden_time, 30)+'前不允许登录'
                            set @status=0
                            return
                        end
                end
            else--商家
                begin
                    select @employee_number = (select employee_number from merchant where merchant_phone_number=@phone_number)
                    if @employee_number is null
                        begin
                            set @hash_password=sys.fn_VarBinToHexStr(HASHBYTES('SHA2_256',@password))
                            set @hash_password_temp=(select password from merchant where merchant_phone_number=@phone_number)
                            if (@hash_password_temp=@hash_password)
                                begin
                                    print STR(@phone_number,11)+LEFT(@username,1)
                                    set @status=1
                                    return
                                end
                            else
                                begin
                                    print '密码错误'
                                    set @status=2
                                    return
                                end
                        end
                    else
                        begin
                            select @forbidden_time = (select forbidden_time from merchant where merchant_phone_number=@phone_number)
                            print CONVERT(varchar(100), @forbidden_time, 30)+'前不允许登录'
                            set @status=0
                            return
                        end
                end
        end
go

